在上一篇文章中，我們介紹了 macro 的概念以及何時需要使用它。本篇將深入探討如何在各種情境中撰寫 macro。

重複語法

當 SQL 中出現大量重複語法時，將其寫成 macro 可以有效減少冗余代碼。

舉例來說，將年齡按每 10 歲分組時，通常需要編寫多個 CASE WHEN 語句。這種情況非常適合使用 macro 來簡化。

# 原本寫法
SELECT
	CASE
		WHEN age < 10 THEN '10 歲以下'
		WHEN age < 20 THEN '10-19 歲'
		WHEN age < 30 THEN '20-29 歲'
		WHEN age < 40 THEN '30-39 歲'
		WHEN age < 50 THEN '40-49 歲'
		WHEN age < 60 THEN '50-59 歲'
		WHEN age >= 60 THEN '60 歲以上'
		END AS age_label
FROM
	member

使用 case_when_interval macro 可以根據傳入的參數靈活調整分組。通過設定 interval 來決定組距、min_value 和 max_value 來確定組數，以及 unit_name 來指定單位。這樣的設計使得 macro 能夠適用於相似情境但條件不同的 model 中。

{%- macro case_when_interval(column, min_value, max_value, interval, unit_name) -%}
	CASE
	{% for unit in range(min_value, max_value+interval, interval) -%}
		{%- if loop.first -%}
			{%- set label = unit ~ unit_name ~ "以下" -%}
		{% else -%}
      {%- set last_unit = unit - interval -%}
      {%- set this_unit = unit - 1 -%}
			{%- set label = last_unit ~ unit_name ~ "-" ~ this_unit ~ unit_name -%}
		{%- endif -%}
		WHEN {{ column }} < {{ unit }} THEN {{ label }}
		{% if loop.last -%}
			{%- set label = unit ~ unit_name ~ "以上" -%}
      WHEN {{ column }} >= {{ unit }} THEN {{ label }}
		{%- endif -%}
	{%- endfor %}
	ELSE NULL END 
{%- endmacro -%}

寫成 macro 後，model 就可以改寫為

SELECT
	{{ case_when_interval(column='age', min_value=10, max_value=60, interval=10, unit_name='歲') }} AS age_lable
FROM
	member

相較於原本需要編寫多行重複語法，改寫成 macro 後只需呼叫一次就能取代大量冗餘代碼。此外，若其他 model 也需要類似功能，只要調整參數即可重複使用此 macro，大幅提高了代碼的可重用性。

常用的轉換邏輯

在不同 models 中經常使用，且無法透過 BigQuery UDF 實現的轉換邏輯，非常適合寫成 macro。

範例：將 isotime 轉換為 datetime 格式。若使用的資料倉儲不像 BigQuery 那樣提供內建 UDF，可以建立一個通用的 macro 來處理這種轉換。

# 原本寫法
SELECT
	DATETIME(PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E*SZ", iso_time), 'Asia/Taipei') AS datetime
FROM
	member

{%- macro isotime_to_datetime(timestamp_str, timezone) -%}
	DATETIME(PARSE_TIMESTAMP("%Y-%m-%dT%H:%M:%E*SZ", {{ timestamp_str }}), '{{ timezone }}')
{%- endmacro -%}

寫成 macro 後，model 就可以改寫為

SELECT
	{{ isotime_to_datetime(timestamp_str="iso_time", timezone="Asia/Taipei") }} AS datetime
FROM
	member

使用查詢結果動態生成查詢

將需要根據一個查詢結果來生成另一個查詢的邏輯寫成 macro 是非常有效的做法。

舉例來說，考慮 SurveyCake 問卷的原始資料結構：每個問卷的每個題目都是一個獨立的行。假設一份問卷有 10 個題目，10 人填答，就會產生 100 行數據。我們的目標是將問卷題目轉置為列，使每一行代表一個人的全部答案。

然而，由於每份問卷的題目代號和數量都可能不同，我們需要先查詢 survey_cake_record 表來確定應該將哪些題目代號轉換為列。這種動態生成查詢的需求正是 macro 的理想應用場景。

WITH raw_data AS (
  SELECT
	  id, # 回答 ID 
	  subject_no, # 題目代號
      answer # 問卷答案
  FROM
    survey_cake_record
  WHERE svid IN ('rxqxB') # 問卷 ID
)
SELECT 
  id,
  _12, _32, _37, _38, _39, _31, _36, _35, _34, _41, _43, _40, _46, _49, _48, _50, _51, _47, _45, _44, _53, _52, _15, _14, _18, _19, _17, _28, _26, _30 # 題目代號
FROM raw_data
PIVOT(
  ANY_VALUE(answer) 
  # 選取要轉成 column 的值（題目代號）
  FOR subject_no IN (
    '_12', '_32', '_37', '_38', '_39', '_31', '_36', '_35', '_34', '_41', '_43', '_40', '_46', '_49', '_48', '_50', '_51', '_47', '_45', '_44', '_53', '_52', '_15', '_14', '_18', '_19', '_17', '_28', '_26', '_30'
  )
)

改寫成 macro，就可以在 macro 中先執行 query 查詢問卷的題目代號並自動填入 query，不需要手動查詢和填寫所有的題目代號

{%- macro parse_surveycake_data(svid_list) -%}
  {%- set query -%} 
    SELECT DISTINCT 
	  subject_no
    FROM 
      survey_cake_record
    WHERE
			svid IN (
        {% for svid in svid_list -%}
          '{{ svid }}' {%- if not loop.last -%}, {% endif %}
        {%- endfor %}
      )
  {%- endset -%}
	
  # 避免 compile 時出錯，需要加 if execute
  {%- if execute -%}
	  # 查詢問卷題目代號
	  {%- set results = run_query(query) -%}
    {%- set column_list = results.columns[0].values() -%}
  {%- endif -%}

  WITH raw_data AS (
    SELECT
      id,
	  subject_no,
	  answer,
    FROM
      survey_cake_record
    WHERE
      svid IN (
        {% for svid in svid_list -%}
          '{{ svid }}' {%- if not loop.last -%}, {% endif %}
        {%- endfor %}
      )
  )
  SELECT 
    id,
    {% for column in column_list -%}
      {{ column }} {%- if not loop.last -%}, {% endif %}
    {%- endfor %}
  FROM raw_data
  PIVOT(
    ANY_VALUE(answer) 
    FOR subject_no IN (
      {% for column in column_list -%}
        '{{ column }}' {%- if not loop.last -%}, {% endif %}
      {%- endfor %}
    )
  )
{%- endmacro -%}

如果 macro 中執行了查詢，必須將執行結果的提取放在 if execute 判斷式中。

在編譯階段，dbt 不會執行 run_query，因此 results 為 None。如果直接訪問 results.columns，會因 NoneType 物件沒有 columns 屬性而報錯。將這段邏輯放入 if execute 中，可以告訴 dbt 這部分只在執行時需要，編譯階段則會忽略。

使用 macro 後，原本的 model 可以簡化為以下簡潔的寫法（變得超級少！）：

{{ 
	parse_surveycake_data(
	  svid_list = ['rxqxB']
 )
}}

如何在 model 中使用 macro

# dbt folders
dbt_project/
│
├── models/
│   └── customer_data.sql
│
└── macros/
    └── case_when_interval.sql

# models/customer_data.sql
SELECT
    customer_id,
    {{ case_when_interval(column='age', min_value=10, max_value=60, interval=10, unit_name='歲') }} AS age_group
FROM
    {{ source('raw_data', 'customers') }}

以上就是三種適合寫 macro 的情境，活用 macro 就可以減少寫很多重複性高的 code，幫助我們提升開發 models 的效率，query 看起來也會較為簡潔。下一篇會介紹 dbt 內建有哪些 macros 及其功用。